/*DONE* Table [dbo].[<CompanyName>$Staff$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 30.7.2020 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Staff$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Staff$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[ID] [nvarchar](20) NOT NULL,
	[Password] [nvarchar](200) NOT NULL,
	[Change Password] [tinyint] NOT NULL,
	[Store No_] [nvarchar](10) NOT NULL,
	[Void Transaction] [int] NOT NULL,
	[Manager Privileges] [int] NOT NULL,
	[XZY-Report Printing] [int] NOT NULL,
	[Tender Declaration] [int] NOT NULL,
	[Floating Declaration] [int] NOT NULL,
	[Price Override] [int] NOT NULL,
	[Max_ Discount to Give _] [decimal](38, 20) NOT NULL,
	[Suspend Transaction] [int] NOT NULL,
	[Max_ Total Discount _] [decimal](38, 20) NOT NULL,
	[Open Draw_ without Sale] [int] NOT NULL,
	[Discount from Perm_ Group] [int] NOT NULL,
	[First Name] [nvarchar](30) NOT NULL,
	[Last Name] [nvarchar](30) NOT NULL,
	[Employment Type] [int] NOT NULL,
	[Fraud Sort Field] [decimal](38, 20) NOT NULL,
	[Last Date Modified] [datetime] NOT NULL,
	[Name on Receipt] [nvarchar](20) NOT NULL,
	[Address] [nvarchar](100) NOT NULL,
	[Address 2] [nvarchar](50) NOT NULL,
	[City] [nvarchar](30) NOT NULL,
	[Post Code] [nvarchar](20) NOT NULL,
	[Home Phone No_] [nvarchar](30) NOT NULL,
	[Work Phone No_] [nvarchar](30) NOT NULL,
	[Hourly Rate] [decimal](38, 20) NOT NULL,
	[Payroll No_] [nvarchar](20) NOT NULL,
	[Blocked] [tinyint] NOT NULL,
	[Date to Be Blocked] [datetime] NOT NULL,
	[Left Handed] [tinyint] NOT NULL,
	[Sales Person] [nvarchar](20) NOT NULL,
	[No_ Series] [nvarchar](20) NOT NULL,
	[Permission Group] [nvarchar](20) NOT NULL,
	[Privacy Blocked] [tinyint] NOT NULL,
	[Return in Transaction] [int] NOT NULL,
	[Void Prepayment] [int] NOT NULL,
	[Void Prepayment Line] [int] NOT NULL,
	[Change Prepayment Amt_] [int] NOT NULL,
	[Add Prepayment Amt_] [int] NOT NULL,
	[Void Line] [int] NOT NULL,
	[Add Payment] [int] NOT NULL,
	[Transfer Orders] [int] NOT NULL,
	[Split Bills] [int] NOT NULL,
	[Switch Dining Table Layout] [int] NOT NULL,
	[Adjust Dining Tables] [int] NOT NULL,
	[Design Dining Table Layout] [int] NOT NULL,
	[Seat Guests] [int] NOT NULL,
	[View Kitchen Status] [int] NOT NULL,
	[Change Staff] [int] NOT NULL,
	[Add Start Float] [int] NOT NULL,
	[Open Sales POS Directly] [int] NOT NULL,
	[Open Sales POS] [int] NOT NULL,
	[Change Dining Tbl_ Status] [int] NOT NULL,
	[Reset Dining Tbl_ Status] [int] NOT NULL,
	[Rush Order in Kitchen] [int] NOT NULL,
	[Edit Available Qty_] [int] NOT NULL,
	[Continue on TS errors] [tinyint] NOT NULL,
	[Last Z-Report] [nvarchar](10) NOT NULL,
	[POS Interface Profile] [nvarchar](10) NOT NULL,
	[Language] [nvarchar](10) NOT NULL,
	[Create Customers] [int] NOT NULL,
	[View Sales History] [int] NOT NULL,
	[Customer Comments] [int] NOT NULL,
	[Max_ Tender Decl_ Difference] [decimal](38, 20) NOT NULL,
	[Max_ Warning Count] [int] NOT NULL,
	[Show Difference in Warning] [int] NOT NULL,
	[Action After Diff_ Warning] [int] NOT NULL,
	[CID-Report Printing] [int] NOT NULL,
	[Show Trans_ Amount] [int] NOT NULL,
	[Update Customers] [int] NOT NULL,
	[TIPS Handling] [int] NOT NULL,
	[Last Y-Report] [nvarchar](10) NOT NULL,
	[Max_ Diff__Warn_ from Perm_ Gr] [int] NOT NULL,
	[Inventory Active] [tinyint] NOT NULL,
	[Inventory Main Menu] [nvarchar](10) NOT NULL,
	[POS Style Profile] [nvarchar](10) NOT NULL,
	[POS Menu Profile] [nvarchar](10) NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[County] [nvarchar](30) NOT NULL,
 CONSTRAINT ['+@Company+'$Staff$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Staff$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END